#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import MySQLdb
import sys

## Connection aux bases de donnees

db1 = MySQLdb.connect(db = "provencedb", user='maxk69', passwd = "Oups,IveLostMyPassword!")
db2 = MySQLdb.connect(db = "maxk69_marketer", user='maxk69', passwd = "Oups,IveLostMyPassword!")

## Definition de la stratégie d'action

strategie_test = [
	('48', ['44'], ['47']),
]

strategie = [ 
	('34', ['24', '47'], ['7', '20']),
	('35', [], ['29']),
	('36', ['14'], ['17', '18', '19']),
	('37', ['48'], ['13', '22', '24']),
	('38', ['43'], ['1', '2', '3', '4', '28', '27', '26']),
	('39', [], ['23']),
	('42', [], ['12']),
]

# Nom des listes

nom_liste = {
	'34': '1-2000 NEUF Hommes jeunes',
	'35': '1-2000 NEUF Hommes adultes',
	'36': '1-2000 ORANGE Femmes',
	'37': '1-2000 WANADOO Femmes',
	'38': '1-2000 ORANGE Hommes',
	'39': '1-2000 WANADOO Hommes',
	'42': '1-2000 FREE Hommes',
}

# Definition de la liste des champs à injecter

champs = [
	"listid",
	"emailaddress",
	"domainname",
	"format",
	"confirmed",
	"confirmcode",
	"requestdate",
	"requestip",
	"confirmdate",
	"confirmip",
	"subscribedate",
	"bounced",
	"unsubscribed",
	"unsubscribeconfirmed",
	"formid"
]

# Definition des fonctions

def traitement_des_resultats(elements_list):
	elements = []
	n_element = 0
	for element in elements_list:
		if n_element == 1:
			element = megaL
		elif n_element == 2:
			current_address = element
		if n_element in (0, 1, 7, 9, 11, 12, 13, 15):
			elements.append(str(element))
		else:
			elements.append('"' + element + '"')
		n_element += 1
	statut = test_du_statut(elements[12:14])
	injecte_moi_tout_ca(",".join(elements[1:]), current_address, statut)

def test_du_statut(stats):
	if stats[1] != '0':
		return('unsubscribed')
	elif stats[0] != '0':
		return('bounced')
	return('valid')

def injecte_moi_tout_ca(str_elements, email_address, stat):
	test_cur = db2.cursor()
	test_cur.execute("SELECT `emailaddress` FROM `em_list_subscribers` WHERE `listid`=" + megaL + " AND `emailaddress`='" + email_address + "'")
	resultat = test_cur.fetchone()
	if resultat == None:
		inject_cur = db2.cursor()
		inject_cur.execute("INSERT INTO `em_list_subscribers` (" + ",".join(champs) + ") VALUES(" + str_elements + ")")
		inject_cur.close()
		incrementation_des_compteurs(stat)
	test_cur.close()

def incrementation_des_compteurs(validornot):
	global valsub, valunsub, valbounce
	if validornot == 'unsubscribed':
		valunsub += 1
	elif validornot == 'bounced':
		valbounce += 1
	else:
		valsub += 1

# Parcours des mega-listes

for operation in strategie:
	megaL = operation[0]
	cur = db2.cursor()
	cur.execute("SELECT `subscribecount`,`unsubscribecount`,`bouncecount` FROM `em_lists` WHERE `listid`=" + megaL)
	valeurs_count = cur.fetchone()
	valsub = valeurs_count[0]
	valunsub = valeurs_count[1]
	valbounce = valeurs_count[2]
	cur.close()

	# Parcours des listes

		# provencedb

	for liste in operation[1]:
		cur2 = db1.cursor()
		cur2.execute("SELECT * FROM `em_list_subscribers` WHERE `listid`=" + liste)
		round = cur2.fetchone()
		while round != None:
			traitement_des_resultats(round)
			round = cur2.fetchone()
		cur2.close()

		# emailmarketer

	for liste in operation[2]:
		cur3 = db2.cursor()
		cur3.execute("SELECT * FROM `em_list_subscribers` WHERE `listid`=" + liste)
		round = cur3.fetchone()
		while round != None:
			traitement_des_resultats(round)
			round = cur3.fetchone()
		cur3.close()

	# Mise à jour des compteurs

	update_cur = db2.cursor()
	update_cur.execute("UPDATE `em_lists` SET `subscribecount`=" + str(valsub) + " WHERE listid=" + megaL)
	update_cur.execute("UPDATE `em_lists` SET `unsubscribecount`=" + str(valunsub) + " WHERE listid=" + megaL)
	update_cur.execute("UPDATE `em_lists` SET `bouncecount`=" + str(valbounce) + " WHERE listid=" + megaL)
	update_cur.close()

# Application des modifications

db2.commit()


